package xpn.platform.modules.template.excel;

import com.google.gson.JsonArray;
import com.google.gson.JsonElement;
import com.google.gson.JsonObject;
import com.google.gson.JsonParser;
import com.lowagie.text.DocumentException;
import com.lowagie.text.pdf.BaseFont;

import jdk.nashorn.api.scripting.ScriptObjectMirror;
import lombok.extern.slf4j.Slf4j;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.ss.usermodel.CellCopyPolicy;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.client.RestClientException;
import org.springframework.web.client.RestTemplate;
import org.xhtmlrenderer.pdf.ITextFontResolver;
import org.xhtmlrenderer.pdf.ITextRenderer;
import xpn.platform.common.config.XpnPlatformConfig;
import xpn.platform.common.exception.XpnBusinessException;
import xpn.platform.common.exception.XpnExceptionCodeEnum;
import xpn.platform.common.rulesengine.SimpleRulesEngine;
import xpn.platform.common.util.ExcelToPdfUtil;
import xpn.platform.common.util.Tool;
import xpn.platform.modules.attachment.AttachmentService;
import xpn.platform.modules.sys.user.SysUserService;

import javax.script.ScriptException;
import javax.servlet.http.HttpServletResponse;

import java.io.*;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * Excel 服务类
 *
 * @author duwei
 */
@SuppressWarnings("restriction")
@Slf4j
@Service
public class ExcelServiceImpl implements ExcelService {

	@Autowired
	ExcelDao excelDao;

	@Autowired
    AttachmentService attachmentService;

	@Autowired
	private SimpleRulesEngine simpleRulesEngine;

	@Autowired
	private RestTemplate restTemplate;

	@Autowired
    XpnPlatformConfig xpnPlatformConfig;

	@Autowired
    SysUserService userService;

	@Override
	public String getTemplateFileName(String templateNo) {
		// 根据templateNo查找模板配置记录record
		Excel record = excelDao.findByNo(templateNo);
		return getFilePath(record);
	}

	public String getFilePath(Excel record) {
		// 2 调用附件服务获取所在的文件夹
		String entityLink = "/template_excel/" + record.getId();
		String folder = attachmentService.getAttachmentFolder(entityLink, null);

		// 3 取文件夹下的第一个文件的全路径名
		String filePath;

		File f = new File(folder);
		File fa[] = f.listFiles();
		List<String> list = new ArrayList<>();

		for (int i = 0; i < fa.length; i++) {
			File fs = fa[i];
			if (!fs.isDirectory()) {
				list.add(fs.getName());
			}
		}
		if (list.size() == 0) {
			throw new XpnBusinessException(XpnExceptionCodeEnum.BUSINESS_EXCEPTION_HTTP_STATUS, "未上传模板文件!");
		}
		filePath = folder + "/" + list.get(0);

		// 4 返回模板文件的全路径名
		return filePath;
	}

	@Override
	public String mailMerge(String templateNo, String attachmentLink, String fileFullName, JsonObject obj,String fileExcelFullName) {

		// 0.检查是否有文件
		// pdf保存路径
		String path = attachmentService.getAttachmentFolder(attachmentLink, "print");
		File filePrint = new File(path);
		if (filePrint.exists()) {
			File[] files = filePrint.listFiles();
			for(File file:files){
				file.delete();
			}
		}
		String filePath = path + "/" + fileFullName;
		File file = new File(filePath);
		if (file.exists()) {
			file.delete();
		}

		String fileExcelPath = path + "/" + fileExcelFullName;
		File fileExcel = new File(fileExcelPath);
		if (fileExcel.exists()) {
			fileExcel.delete();
		}

		// 1 根据模板编号templateNo获得计算规则rule
		Excel excel = excelDao.findByNo(templateNo);

		// 获取模板配置信息
		ScriptObjectMirror mirrorResult = getTemplateData(excel);

		// 4 根据模板编号templateNo获得excel模板文件
		// 获取模板文件
		String templateFile = this.getFilePath(excel);
		File template = new File(templateFile);

		XSSFWorkbook xssfWorkbook;

		try {
			FileInputStream input = new FileInputStream(template);
			xssfWorkbook = new XSSFWorkbook(input);

			XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);

			// 5 根据result填充模板文件
			for (Object o : mirrorResult.values()) {
				ScriptObjectMirror objectMirror = (ScriptObjectMirror) o;
				Integer row = (Integer) objectMirror.get("row");
				Integer col = (Integer) objectMirror.get("col");
				String key = (String) objectMirror.get("key");
				String type = (String) objectMirror.get("type");
				String header = (String) objectMirror.get("header");
				String keyname = (String) objectMirror.get("keyname");
				Object commentNoObj = objectMirror.get("commentNo");
				String commentNo = commentNoObj != null ? (String) commentNoObj : "";
				Object round= objectMirror.get("round");
				String value = "";
				if(type==null || !type.equals("list") || !type.equals("xlist")){
					value = getValueInObj(key, commentNo, obj,round!=null? (Integer)round :null);
					if (value==null) {
						value="";
					}
				}
				XSSFRow xssfRow = xssfSheet.getRow(row - 1);
				if(xssfRow==null)
					xssfRow = xssfSheet.createRow(row - 1);
				XSSFCell cell = xssfRow.getCell(col - 1);
				
				if(cell==null)  cell=xssfRow.createCell(col - 1);
				if (cell != null) {
					short charRows=getHeightByValue(value,objectMirror.get("charNumInLine"));
					short height=5000;
	                if (charRows <= 1 && charRows >= 0)
						height=510;
	                else if (charRows > 1)
						height=(short)(510 + charRows * 230);
					short oldHeight=xssfRow.getHeight();
					if(oldHeight<height){
						xssfRow.setHeight(height);
					}
					
					if(keyname==null){
						if(type==null){
							cell.setCellValue(value);
						}else{
							if(type.equals("list")){//视图列表
								JSONArray viewList = getValuesInObj(obj);
								Object addTotal = objectMirror.get("addTotal");
								Boolean hasTotal = addTotal==null ? false : (Boolean)addTotal;
								int i=0;
								BigDecimal total = BigDecimal.ZERO;
								for (Object v : viewList) {
									JsonObject jsono = new JsonParser().parse(v.toString()).getAsJsonObject();
									String obj2= getValueInObj(key, null, jsono,round!=null? (Integer)round :null);
									if (StringUtils.isNotBlank(obj2)){
										value = obj2.replaceAll("\"", "");
										if (hasTotal) {
											total = total.add(BigDecimal.valueOf(Double.parseDouble(obj2)));
										}
									} else {
										value = "";
									}
									cell.setCellValue(value);
									cell = xssfRow.getCell(col+i);
									i++;
								}
								if (hasTotal) {
									DecimalFormat df2 =new DecimalFormat("0.00");
									cell.setCellValue(df2.format(total));
								}
							}else if(type.equals("sumlist")){//视图合计
								JSONArray values = getValuesInObj(obj);
								int num = values.size();
								cell = xssfRow.getCell(col+num);
								if(key.equals("sumName")){
									cell.setCellValue("合计");
								}else{
									cell.setCellValue(value);
								}
							}else if(type.equals("xlist")){//视图列表
								JSONArray viewList = getValuesInObj(obj);
								int i = 0;
								if(viewList.size()!=0){
									if(header!=null && header.equals("y")){
										for (Object v : viewList) {
											xssfSheet.copyRows(row-1+i, row+i, row+i, new CellCopyPolicy());
											i++;
										}
									}
								}else{
									xssfRow = xssfSheet.createRow(row);
									xssfSheet.removeRow(xssfRow);
								}
								
								i = 0;
								for (Object v : viewList) {
									JsonObject jsono = new JsonParser().parse(v.toString()).getAsJsonObject();
									JsonElement obj2= jsono.get(key);
									if (obj2 != null && !obj2.isJsonNull()){
										value = obj2.toString().replaceAll("\"", "");
									} else {
										value = "";
									}
									xssfRow = xssfSheet.getRow(row - 1 +i);
									cell = xssfRow.getCell(col-1);
									cell.setCellValue(value);
									i++;
								}
							}
							
						}
					}else{
						String keyValue = cell.getStringCellValue();
						keyValue = keyValue.replace("{"+keyname+"}",value);
						cell.setCellValue(keyValue);
					}
				}
			}
			xssfWorkbook.write(new FileOutputStream(fileExcelPath));
			ByteArrayOutputStream os = new ByteArrayOutputStream();
			xssfWorkbook.write(os);
			ExcelToPdfUtil.getInstance().convertToFile(os, filePath);
			input.close();
			xssfWorkbook.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return filePath;
	}

	public XSSFCell getCell(Integer row,Integer col,XSSFSheet xssfSheet){
		XSSFRow xssfRow = xssfSheet.getRow(row - 1);
		if(xssfRow==null)
			xssfRow = xssfSheet.createRow(row - 1);
		XSSFCell cell = xssfRow.getCell(col - 1);
		if(cell==null)
			cell=xssfRow.createCell(col - 1);
		return cell;
	}

	public short getHeightByValue(String value,Object charNumInLineObj){
		double charNumInLine=25.0;
		if(charNumInLineObj!=null)
			charNumInLine=(Integer)charNumInLineObj;
		return (short)Math.ceil(value.length()/charNumInLine);
	}

	public String executeFunctionAndRule(String value,String executionFunction,String executionRule){
		Object result = null;
		try {
			simpleRulesEngine.executeRule(executionFunction);
			result=simpleRulesEngine.executeRule(executionRule);
		} catch (RestClientException | ScriptException e) {
			e.printStackTrace();
		}
		value=(String)result;
		return value;
	}

	@Override
	public void catalog(String templateNo, String datasource, HttpServletResponse response,Long userId,String extractColumnKeys) throws IOException {

		// 1 构造结果文件
		Excel excel = excelDao.findByNo(templateNo);

		String fileName = excel.getName()+".xlsx";

		String folder = xpnPlatformConfig.getAttachment().getRoot() + "/temp/" + userId;
		File f = new File(folder);
		if (!f.exists()) {
			f.mkdirs();
		}

		File exportFile = new File(folder + "/" + fileName);
		if (exportFile.exists())
			exportFile.delete();
		exportFile.createNewFile();

		// 2 获取模板文件
		String templateFile = this.getFilePath(excel);
		File template = new File(templateFile);
		XSSFWorkbook xssfWorkbook;

		// 3 导出数据到结果文件
		try {
			// 3.1 创建结果文件
			FileOutputStream out = new FileOutputStream(exportFile);
			FileInputStream input = new FileInputStream(template);
			xssfWorkbook = new XSSFWorkbook(input);

			XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);

			// 3.2 获取导出的数据
			String stringExecutor = restTemplate.getForObject(datasource, String.class);

			// 3.3 获取配置信息
			ScriptObjectMirror mirrorResult = getTemplateData(excel);

			JsonArray dataArray = new JsonArray();
			// 从配置信息里面获取数组在数据中的 key
			if(mirrorResult.get("dataArrayKey")!=null){
				JsonObject obj = new JsonParser().parse(stringExecutor).getAsJsonObject();
				String dataArrayKey = (String) mirrorResult.get("dataArrayKey");
				JsonElement dataObj=getElementInObj(dataArrayKey, obj);
				if(dataObj!=null&&!dataObj.isJsonNull())
					dataArray = dataObj.getAsJsonArray();
			}else{
				dataArray= new JsonParser().parse(stringExecutor).getAsJsonArray();
			}

			// 从配置信息里面获取列获取值信息
			ScriptObjectMirror columnKeys = (ScriptObjectMirror) mirrorResult.get("columnKeys");

			// 从配置信息里面获取固定区域信息
			ScriptObjectMirror fixedKeys = null;
			Object fixedKeysObj=mirrorResult.get("fixedKeys");
			if(fixedKeysObj!=null)
				fixedKeys=(ScriptObjectMirror)fixedKeysObj;

			// 从配置信息里面获取扩展列信息
			ScriptObjectMirror extractColumn = null;
			Object extractColumnObj=mirrorResult.get("extractColumn");
			if(extractColumnObj!=null)
				extractColumn=(ScriptObjectMirror)extractColumnObj;

					// 从配置信息里面获取插入数据开始的行
			Integer startRow = (Integer) mirrorResult.get("startRow");

			//在表格中插入活动的配置列头
			JsonArray extractColumnArray=new JsonArray();
			if(!extractColumnKeys.equals("")&&extractColumn!=null){
				//从配置的扩展列中过滤出前端选择的扩展列
				filterExtractColumn(extractColumnKeys,extractColumn,extractColumnArray);
				XSSFRow xssfRow = xssfSheet.getRow(startRow -2);
				if(xssfRow==null)
					xssfRow = xssfSheet.createRow(startRow -2);
				for(int col = 0; col < extractColumnArray.size(); col++){
					XSSFCell cell = xssfRow.createCell(columnKeys.values().size()+col);
					if (cell != null) {
						cell.setCellValue(extractColumnArray.get(col).getAsJsonObject().get("name").getAsString());
					}
				}

			}




			// 3.4 循环数据数组，把每行数据插入指定行
			for (int row = 0; row < dataArray.size(); row++) {
				JsonObject data = dataArray.get(row).getAsJsonObject();
				//注入当前对象
				SimpleRulesEngine.InjectedObject varRecord = new SimpleRulesEngine.InjectedObject(data, "xpnExportCurRecord"); // 当前用户
				simpleRulesEngine.injectObjects(varRecord);

				//给固定的模板中插入值
				if(fixedKeys!=null)
				for (Object o : fixedKeys.values()) {
					ScriptObjectMirror objectMirror = (ScriptObjectMirror) o;
					Integer rowFixed = (Integer) objectMirror.get("row");
					Integer colFixed = (Integer) objectMirror.get("col");
					String key = (String) objectMirror.get("key");
					Object round= objectMirror.get("round");
					String value = getValueInObj(key, "", data,round!=null? (Integer)round :null);
					XSSFCell cell = getCell(rowFixed,colFixed,xssfSheet);
					if (cell != null) {
						cell.setCellValue(value);
					}
				}

				XSSFRow xssfRow = xssfSheet.createRow(startRow + row - 1);
				// 循环每一列的值插入到当前行
				int column = 0;
				for (Object o : columnKeys.values()) {
					ScriptObjectMirror objectMirror = (ScriptObjectMirror) o;
					String key = (String) objectMirror.get("key");
					Object executionFunction =objectMirror.get("executionFunction");
					Object executionRule =objectMirror.get("executionRule");
					Object round= objectMirror.get("round");
					String value = getValueInObj(key, "", data,round!=null? (Integer)round :null);
					//如果配置是序号，就直接给序号
					if(key.equals("index")){
						value=String.valueOf(row+1);
					}
					if(executionFunction!=null&&executionRule!=null){
                        //如果有运行函数 就注入函数，执行函数
						value=executeFunctionAndRule(value,(String)executionFunction,(String)executionRule);
					}
					XSSFCell cell = xssfRow.createCell(column);
					if (cell != null) {
						cell.setCellValue(value);
					}
					//如果有分组就分组,在最后一行时。
					if(row==dataArray.size()-1) {
						Object isGroup = objectMirror.get("isGroup");
						Object totalGroupColumn = objectMirror.get("totalGroupColumn");
						Object roundGroup= objectMirror.get("round");
						Object groupColumn = objectMirror.get("groupColumn");
						Object addTotal = objectMirror.get("addTotal");
						XSSFCellStyle style = xssfWorkbook.createCellStyle();
						style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直
						style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平
						//分组 整个过程是最后一行倒数往上走
						if (isGroup != null||groupColumn!=null) {
							Boolean group = (Boolean)(isGroup!=null?isGroup:false);
							Integer groupColumnInt=column;
							//分组列不为空的情况下，就代表要分组。
							if(groupColumn!=null){
								group=true;
								groupColumnInt=(Integer)groupColumn;
							}
							if (group == true) {
								Integer groupNowRow=startRow + row - 1;
								xssfSheet.getRow(groupNowRow).getCell(column).setCellStyle(style);
								Integer groupPreRow=startRow + row - 2;
								String nowCellValue = xssfSheet.getRow(groupNowRow).getCell(groupColumnInt).getStringCellValue();
								while (groupPreRow>=startRow-1){
									String preCellValue = xssfSheet.getRow(groupPreRow).getCell(groupColumnInt).getStringCellValue();
									//与上一行的值不相等就代表可以合并
									if (!preCellValue.equals(nowCellValue)) {
										//多余一行才合并
										if(groupNowRow-groupPreRow>1){
											xssfSheet.addMergedRegion(new CellRangeAddress(groupPreRow+1, groupNowRow, column, column));
										}
										nowCellValue = preCellValue;
										groupNowRow=groupPreRow;
									}else{
										//上一行与初始行相等的情况合并
										if (groupPreRow==startRow-1) {
											xssfSheet.addMergedRegion(new CellRangeAddress(groupPreRow, groupNowRow, column, column));
										}
									}
									xssfSheet.getRow(groupPreRow).getCell(column).setCellStyle(style);
									groupPreRow=groupPreRow-1;
								}
							}
						}
						//分组合计
						if(totalGroupColumn!=null){
							Integer totalGroupColumnInt=(Integer)totalGroupColumn;
							Integer groupNowRow=startRow + row - 1;
							xssfSheet.getRow(groupNowRow).getCell(column).setCellStyle(style);
							Integer groupPreRow=startRow + row - 2;
							String nowCellValue = xssfSheet.getRow(groupNowRow).getCell(totalGroupColumnInt).getStringCellValue();

							Double nowTotal=getDoubleValueInCell(xssfSheet.getRow(groupNowRow).getCell(column));
							while (groupPreRow>=startRow-1){
								String preCellValue = xssfSheet.getRow(groupPreRow).getCell(totalGroupColumnInt).getStringCellValue();
								//与上一行的值不相等就代表可以合并
								if (!preCellValue.equals(nowCellValue)) {
									//多余一行才合并
									if(groupNowRow-groupPreRow>1){
										xssfSheet.addMergedRegion(new CellRangeAddress(groupPreRow+1, groupNowRow, column, column));
										xssfSheet.getRow(groupPreRow+1).getCell(column).setCellValue(formatDoubleRound(nowTotal,roundGroup!=null?(Integer) roundGroup:null));
									}
									nowCellValue = preCellValue;
									groupNowRow=groupPreRow;
									nowTotal=getDoubleValueInCell(xssfSheet.getRow(groupNowRow).getCell(column));
								}else{
									//相等的情况下相加合计值
									nowTotal=nowTotal+getDoubleValueInCell(xssfSheet.getRow(groupPreRow).getCell(column));
									//初始行相等的情况合并
									if (groupPreRow==startRow-1) {
										xssfSheet.addMergedRegion(new CellRangeAddress(groupPreRow, groupNowRow, column, column));
										xssfSheet.getRow(groupPreRow).getCell(column).setCellValue(formatDoubleRound(nowTotal,roundGroup!=null?(Integer) roundGroup:null));
									}
								}
								xssfSheet.getRow(groupPreRow).getCell(column).setCellStyle(style);
								groupPreRow=groupPreRow-1;
							}
						}
						//合计行
						if(addTotal!=null&&(Boolean) addTotal){
							Integer groupNowRow=startRow + row - 1;
							Double nowTotal=Double.parseDouble("0");
							while (groupNowRow>=startRow-1){
								nowTotal=nowTotal+getDoubleValueInCell(xssfSheet.getRow(groupNowRow).getCell(column));
								groupNowRow=groupNowRow-1;
							}
							XSSFRow totalRow =xssfSheet.getRow(startRow + row) ;
							if(totalRow==null)
								totalRow =xssfSheet.createRow(startRow + row);
							//设置合计的文字（在第一列）
							XSSFCell totalTextCell = totalRow.getCell(0);
							if(totalTextCell==null)
								totalTextCell=totalRow.createCell(0);
							totalTextCell.setCellValue("合计");
							//设置合计值
							XSSFCell totalValueCell = totalRow.createCell(column);
							totalValueCell.setCellValue(nowTotal);
						}
					}
					column++;
				}

				//循环活动列，插入活动列的值
				for(JsonElement je:extractColumnArray){
					JsonObject objItem=je.getAsJsonObject();
					String key = objItem.get("key").getAsString();
					JsonElement executionFunction =objItem.get("executionFunction");
					JsonElement executionRule =objItem.get("executionRule");
					Object round= objItem.get("round");
					String value = getValueInObj(key, "", data,round!=null? (Integer)round :null);
					if(executionFunction!=null&&!executionFunction.isJsonNull()&&executionRule!=null&&!executionRule.isJsonNull()){
						//如果有运行函数 就注入函数，执行函数
						value=executeFunctionAndRule(value,executionFunction.getAsString(),executionRule.getAsString());
					}
					XSSFCell cell = xssfRow.createCell(column);
					if (cell != null) {
						cell.setCellValue(value);
					}
					column++;
				}
			}

			// 3.5 关闭文件
			xssfWorkbook.write(out);
			input.close();
			out.close();
			xssfWorkbook.close();

			if (response == null)
				return;

			// 4 导出给前端浏览器
			response.setHeader("Content-Type:", "multipart/form-data");
			response.setHeader("Content-Disposition", "attachment;filename="
					+ URLEncoder.encode(new String(fileName.getBytes("utf-8"), "iso8859-1"), "iso8859-1"));
			response.setHeader("Accept-Ranges: ", "bytes");
			response.setHeader("Cache-Control: ", "must-revalidate, post-check=0, pre-check=0");
			response.setHeader("Pragma: ", " no-cache");
			response.setHeader("Expires: ", "0");

			OutputStream os = response.getOutputStream();
			FileInputStream inputExport = new FileInputStream(exportFile);
			byte[] bin = new byte[1024 * 1024];

			for (int i = 0; i != -1;) {
				i = inputExport.read(bin);
				if (i != -1)
					os.write(bin, 0, i);
				else {
					inputExport.close();
					os.flush();
					os.close();
				}
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	public Double getDoubleValueInCell(XSSFCell cell){
		String str="";
		try {
			str = cell.getStringCellValue();
		}catch (Exception e){
			str = String.valueOf(cell.getNumericCellValue());
		}
		if(str.equals(""))
			return  Double.parseDouble("0");
		return Double.parseDouble(str);
	}

	public String formatDoubleRound(Double data,Integer round){
		String value=String.valueOf(data);
		try {
			if(round!=null&&round>0){
				StringBuilder format=new StringBuilder();
				format.append("#0.");
				for (int i=0;i<round;i++){
					format.append("0");
				}
				DecimalFormat df   = new DecimalFormat(format.toString());
				value=df.format(data).toString();
			}
		} catch (Exception e){

		}
		return value;
	}

	@Override
	public ScriptObjectMirror getExtractColumn(String templateNo) {
		// 1 构造结果文件
		Excel excel = excelDao.findByNo(templateNo);
		// 3.3 获取配置信息
		ScriptObjectMirror mirrorResult = getTemplateData(excel);

		// 从配置信息里面获取扩展列信息
		ScriptObjectMirror extractColumn = null;
		Object extractColumnObj=mirrorResult.get("extractColumn");
		if(extractColumnObj!=null)
			extractColumn=(ScriptObjectMirror)extractColumnObj;
		return extractColumn;
	}

	private void filterExtractColumn(String extractColumnKeys,ScriptObjectMirror extractColumn,JsonArray extractColumnArray){
		String[] extractColumnKeysArray = extractColumnKeys.split(";");
		for (Object o : extractColumn.values()) {
			ScriptObjectMirror objectMirror = (ScriptObjectMirror) o;
			String key = (String) objectMirror.get("key");
			String name = (String) objectMirror.get("name");
			Object executionFunction =objectMirror.get("executionFunction");
			Object executionRule =objectMirror.get("executionRule");
			for (String extractColumnKey : extractColumnKeysArray){
				if(extractColumnKey.equals(key)){
					JsonObject extractColumnJsonObj=new JsonObject();
					extractColumnJsonObj.addProperty("key",key);
					extractColumnJsonObj.addProperty("name",name);
					if(executionFunction!=null)
						extractColumnJsonObj.addProperty("executionFunction",(String)executionFunction);
					if(executionRule!=null)
						extractColumnJsonObj.addProperty("executionRule",(String)executionRule);
					extractColumnArray.add(extractColumnJsonObj);
				}
			}
		}
	}

	private ScriptObjectMirror getTemplateData(Excel excel) {
		if (excel == null) {
			throw new XpnBusinessException(XpnExceptionCodeEnum.BUSINESS_EXCEPTION_HTTP_STATUS, "未添加导出数据模板记录!");
		}
		String excelRule = excel.getRules();
		if (excelRule == null || excelRule.isEmpty()) {
			throw new XpnBusinessException(XpnExceptionCodeEnum.BUSINESS_EXCEPTION_HTTP_STATUS, "未配置模板运算规则!");
		}
		Object result = null;
		try {
			result = simpleRulesEngine.executeRule(excelRule);
		} catch (RestClientException | ScriptException e) {
			e.printStackTrace();
			log.error("运算模板规则出错!", e);
		}

		if (result == null) {
			throw new XpnBusinessException(XpnExceptionCodeEnum.BUSINESS_EXCEPTION_HTTP_STATUS, "用户不匹配!");
		}

		ScriptObjectMirror mirrorResult = (ScriptObjectMirror) result;
		return mirrorResult;
	}

	private JsonElement getElementInObj(String key, JsonObject obj) {
		String[] keys = key.split("\\.");
		JsonElement resultObj = null;
		for (String subKey : keys) {
			if (resultObj == null) {
				resultObj = obj.get(subKey);
			} else {
				if (resultObj.isJsonObject())
					resultObj = resultObj.getAsJsonObject().get(subKey);
			}
		}
		return resultObj;
	}

	private String getValueInObj(String key, String commentNo, JsonObject obj,Integer round) {
		String value = "";
		if (key.equals("now"))
			return Tool.getDateString(new Date());

		JsonElement resultObj = getElementInObj(key, obj);
		if(key.equals("orgName")){
			resultObj = getElementInObj("_embedded.createdBy.orgFullName", obj);
		}

		if (resultObj != null && !resultObj.isJsonNull() && !resultObj.isJsonArray() && !resultObj.isJsonObject()){
			value=resultObj.getAsString();
			try {
				if(round!=null&&round>0){
					StringBuilder format=new StringBuilder();
					format.append("#0.");
					for (int i=0;i<round;i++){
						format.append("0");
					}
					DecimalFormat df   = new DecimalFormat(format.toString());
					Double resultDouble=resultObj.getAsDouble();
					value=df.format(resultDouble).toString();
				}
			} catch (Exception e){

			}
			if(key.equals("orgName")){
				value=value.split("/")[0];
			}
		}

		// 如果有意见编号，就要取意见
		if (commentNo != null && !commentNo.equals("") && resultObj != null && !resultObj.isJsonNull()
				&& resultObj.isJsonArray()) {
			// 获取待办
			JsonArray todoList = resultObj.getAsJsonArray();
			value = "";
			StringBuffer bufferReturn = new StringBuffer();
			// 从第一条待办中获取意见
			if (todoList != null && todoList.size() > 0) {
				JsonObject todo = todoList.get(0).getAsJsonObject();
				JsonArray comments = todo.get("comments").isJsonNull()?new JsonArray():todo.get("comments").getAsJsonArray();
				//通过状态id分组,只找出不同状态下的最有一条意见打印。
				Map<String,StringBuffer> mapByStateId = new HashMap<>();
				for (JsonElement o : comments) {
					JsonObject comment = o.getAsJsonObject();
					JsonObject commentColumn = comment.getAsJsonObject("column");
					String no = commentColumn.get("no").getAsString();
					if (no.equals(commentNo)) {
						String stateId ="-1";
						JsonObject todoInComments = comment.getAsJsonObject("todo");
						JsonObject state = todoInComments!=null?todoInComments.getAsJsonObject("state"):null;
						stateId = state!=null?state.get("id").getAsString():"-1";
						StringBuffer buffer = new StringBuffer();
						buffer.append(comment.get("content") == null || comment.get("content").isJsonNull() ? ""
								: comment.get("content").getAsString());
						buffer.append("             ");
						buffer.append(comment.get("userName") == null || comment.get("userName").isJsonNull() ? ""
								: comment.get("userName").getAsString());
						buffer.append("/");
						buffer.append(comment.get("userName") == null || comment.get("userName").isJsonNull() ? ""
								: comment.get("userOrgName").getAsString().split("/")[0]);
						buffer.append("    ");
						buffer.append(comment.get("createdTime") == null || comment.get("createdTime").isJsonNull() ? ""
								: comment.get("createdTime").getAsString());
						mapByStateId.put(stateId,buffer);
					}
				}
				Set<String> stringSet = mapByStateId.keySet();
				for(String keyStateId:stringSet){
					if(bufferReturn.toString().equals("")){
						bufferReturn = mapByStateId.get(keyStateId);
					}else{
						bufferReturn.append("\n");
						bufferReturn.append(mapByStateId.get(keyStateId));
					}
				}
			}
			value = bufferReturn.toString();
		}
		return value;
	}
	private JSONArray getValuesInObj(JsonObject obj) {
		String viewListStr = getElementInObj("viewList", obj).toString();
		JSONArray viewList = JSONArray.fromObject(viewListStr);
		return viewList;
	}
	
	@Override
	public String createHtmlToPdf(String templateNo, String attachmentLink, String fileFullName, JsonObject obj){

		String fileurl = xpnPlatformConfig.getAttachment().getRoot();
		String output=fileurl+"/patrol_print/";
		File file = new File(output);
		if(file.exists()){
			file.delete();
		}
		file.mkdirs();
		String furl = "C:/Windows/Fonts/msyh.ttf";
		file = new File(furl);
		if(!file.exists()){
			furl = "C:/Windows/Fonts/msyh.ttc";
		}
		String outputFile = output+templateNo+".pdf";  
        OutputStream os;
		try {
			os = new FileOutputStream(outputFile);
			ITextRenderer renderer = new ITextRenderer();  
	        ITextFontResolver fontResolver = renderer.getFontResolver();    
	        fontResolver.addFont(furl, BaseFont.IDENTITY_H, BaseFont.NOT_EMBEDDED);  
	        StringBuffer html = new StringBuffer();
	     // DOCTYPE 必需写否则类似于 这样的字符解析会出现错误  
	        html.append("<!DOCTYPE html PUBLIC '-//W3C//DTD XHTML 1.0 Transitional//EN' 'http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd'>");  
	        html.append("<html xmlns='http://www.w3.org/1999/xhtml'>").append("<head>")  
	            .append("<meta http-equiv='Content-Type' content='text/html; charset=UTF-8' />")  
	            .append("<style type='text/css'>@page{size:297mm 297mm;}body "
	            		+ "{font-family:'Microsoft YaHei';font-weight:bold;font-size:16px;}table{border:1px solid #000;}"
	            		+ "td{border:1px solid #000;text-align:left;}.imgdiv{border-top: 1px solid #000;"
	            		+ "border-left:1px solid #000;border-right: 1px solid #000;text-align:center;}</style>")  
	            .append("</head>")  
	            .append("<body>");
 
	 
	        
	        
	    	// 1 根据模板编号templateNo获得计算规则rule
			Excel excelJson = excelDao.findByNo(templateNo);
	        String rules = excelJson.getRules();
	        JSONArray rows = JSONArray.fromObject(rules);
	        //System.out.println("rows:"+rows);
	        //定义打印页面的高度和宽度
	        int htmlHeidth = 842;
	        Double pageRows=30.0;  
	        Integer headRows=0;
	        Integer footRows=0;       	        
	        int dynamicRows=0;
	        
	        int page=1;
	        
	        StringBuffer headRowsInfo = new StringBuffer();
	        ArrayList<String> dynamicRowsInfo = new ArrayList<String>();
	        StringBuffer dynamicRowsStr = new StringBuffer();  
	        StringBuffer footRowsInfo = new StringBuffer();
	        StringBuffer nomalRowsInfo=new StringBuffer();
	        for(int i = 0; i < rows.size(); i++) {
	        	JSONObject rowObj = rows.getJSONObject(i);
	        	String type = (String)rowObj.get("type");
	        	String isDynamic = (String)rowObj.get("isDynamic");
	        	if(isDynamic!=null&&!isDynamic.equals("")){
	        	 	String listKey = (String)rowObj.get("listKey");
	        	 	JsonElement json=getElementInObj(listKey,obj);
	        	 	JSONArray viewList=JSONArray.fromObject(json.toString());
	        	 	dynamicRows=viewList.size();
		        	JSONArray cols = (JSONArray)rowObj.get("cols");
	        		for(int x=0;x<viewList.size();x++){
	        			JSONObject viewRow=viewList.getJSONObject(x);
	        	        StringBuffer dynamicRow = new StringBuffer();
	        	        dynamicRow.append("<tr>");
	    	        	for (int j = 0; j < cols.size(); j++) {
	    	        		String value="";
	    	        		JSONObject colObj = cols.getJSONObject(j);
	    	        		String colspan = (String) colObj.get("colspan");
	    	        		int cs = Integer.parseInt(colspan);
	    	        		String key = (String) colObj.get("key");
	    	        		String keyName = (String) colObj.get("keyName");
	    	        		Object commentNoObj = (String)colObj.get("commentNo");
	    	        		String commentNo = commentNoObj != null ? (String) commentNoObj : "";
	    	        		if(keyName != null){
	    	        			dynamicRow.append("<td style='text-align:center;' height='20px' colspan='" + cs + "' width='"+cs*30+"px'>"+ keyName +"</td>");
	    	        		}else if(key != null){
	    	        			if(viewRow.has(key)){
		    	        			value =viewRow.getString(key);    
		    	        			if(value==null||value.equals("null")||value.equals("")) value="";
	    	        			}
	    	        			dynamicRow.append("<td style='text-align:center;' height='20px' colspan='" + cs + "' width='"+cs*30+"px'>"+value+"</td>");
	    	        		}
	    				}
	    	        	dynamicRow.append("</tr>");
	    	        	dynamicRowsStr.append(dynamicRow);
	    	        	dynamicRowsInfo.add(dynamicRow.toString());
	        		}
	        	}
	        	if(type!=null&&!type.equals("")){
		        	JSONArray cols = (JSONArray)rowObj.get("cols");
		        	if(type.equals("header")){
			        	headRowsInfo.append("<tr>");       		
		        	}
		        	if(type.equals("footer")){
			        	footRowsInfo.append("<tr>");       		
		        	}
		        	for (int j = 0; j < cols.size(); j++) {
		        		String value="";
		        		JSONObject colObj = cols.getJSONObject(j);
		        		String colspan = (String) colObj.get("colspan");
		        		int cs = Integer.parseInt(colspan);
		        		String key = (String) colObj.get("key");
		        		String keyName = (String) colObj.get("keyName");
		        		Object commentNoObj = (String)colObj.get("commentNo");
		        		String commentNo = commentNoObj != null ? (String) commentNoObj : "";
		        		if(keyName != null){
				        	if(type.equals("header")){
			        			headRowsInfo.append("<td style='text-align:center;' height='20px' colspan='" + cs + "' width='"+cs*50+"px'>"+ keyName +"</td>");
				        	}
				        	if(type.equals("footer")){
			        			footRowsInfo.append("<td style='text-align:center;' height='20px' colspan='" + cs + "' width='"+cs*50+"px'>"+ keyName +"</td>");
				        	}else{
				        		
				        	}
		        		}else if(key != null){
		        			value = getValueInObjByHtml(key,commentNo,obj,0);
				        	if(type.equals("header")){
			        			headRowsInfo.append("<td style='text-align:center;' height='20px' colspan='" + cs + "' width='"+cs*50+"px'>"+value+"</td>");
				        	}
				        	if(type.equals("footer")){
			        			footRowsInfo.append("<td style='text-align:center;' height='20px' colspan='" + cs + "' width='"+cs*50+"px'>"+value+"</td>");
				        	}
		        		}
					}
		        	if(type.equals("header")){
			        	headRowsInfo.append("</tr>");		        	
		        		headRows++;	        		
		        	}
		        	if(type.equals("footer")){
			        	footRowsInfo.append("</tr>");		        	
		        		footRows++;	        		
		        	}

	        	}
	        }
	        
	        if(dynamicRowsInfo.size()>0){
	    		double pages=(double)((double)(dynamicRowsInfo.size())/pageRows);//转换为天
	    		page=(int) Math.ceil(pages);//转化为整数
	    		if(page<=0){
	    			page=1;
	    		}	 
	        }
	        for (int i=1;i<=page;i++){
	        	if(i==1){
		            html.append("<div><table width='1000px' cellspacing='0' height='930px' cellpadding='0'>");        		
	        	}
	        	else{
		            html.append("<div style='page-break-after:always'><table width='1000px' cellspacing='0' height='930px' cellpadding='0'>");        		       		
	        	}
	            html.append(headRowsInfo);
	            for(int j=0;j<pageRows.intValue();j++){
	            	int index=(i-1)*pageRows.intValue()+j;
	            	if(index>(dynamicRowsInfo.size()-1)) break;
	            	String dynamicRow=dynamicRowsInfo.get(index);
	            	html.append(dynamicRow);
	            }
	            //html.append(dynamicRowsStr);
	            html.append(footRowsInfo);
	            html.append("</table></div>");
	        }
	        html.append("</body></html>");  
	        System.out.println(html.toString());
	        renderer.setDocumentFromString(html.toString()); 
	        renderer.layout();
	        renderer.createPDF(os);  
	        os.close(); 
		} catch (FileNotFoundException e) {
			e.printStackTrace();
			return "";
		} catch (DocumentException e) {
			e.printStackTrace();
			return "";
		} catch (IOException e) {
			e.printStackTrace();
			return "";
		}
		return templateNo+".pdf";
	}
	
	
	@Override
	public String createHtmlToPdfNew(String templateNo, String attachmentLink, String fileFullName, JsonObject obj){

		String fileurl = xpnPlatformConfig.getAttachment().getRoot();
		String output=fileurl+"/patrol_print/";
		File file = new File(output);
		if(file.exists()){
			file.delete();
		}
		file.mkdirs();
		String furl = "C:/Windows/Fonts/msyh.ttf";
		file = new File(furl);
		if(!file.exists()){
			furl = "C:/Windows/Fonts/msyh.ttc";
		}
		String outputFile = output+templateNo+".pdf";  
        OutputStream os;
		try {
			os = new FileOutputStream(outputFile);
			ITextRenderer renderer = new ITextRenderer();  
	        ITextFontResolver fontResolver = renderer.getFontResolver();    
	        fontResolver.addFont(furl, BaseFont.IDENTITY_H, BaseFont.NOT_EMBEDDED);  
	        StringBuffer html = new StringBuffer();
	        //DOCTYPE 必需写否则类似于 这样的字符解析会出现错误  
	        html.append("<!DOCTYPE html PUBLIC '-//W3C//DTD XHTML 1.0 Transitional//EN' 'http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd'>");  
	        html.append("<html xmlns='http://www.w3.org/1999/xhtml'>").append("<head>")  
	            .append("<meta http-equiv='Content-Type' content='text/html; charset=UTF-8' />")  
	            .append("<style type='text/css'>@page{size:297mm 297mm;}body "
	            		+ "{font-family:'Microsoft YaHei';font-weight:bold;font-size:16px;}table{border:1px solid #000;table-layout:fixed;word-wrap:break-strict;}"
	            		+ "td{border:1px solid #000;text-align:left;word-wrap:break-word}.imgdiv{border-top: 1px solid #000;"
	            		+ "border-left:1px solid #000;border-right: 1px solid #000;text-align:center;}</style>")  
	            .append("</head>")  
	            .append("<body>");
            html.append("<div><table width='1000px' cellspacing='0' height='930px' cellpadding='0'>");  	        
	    	// 1 根据模板编号templateNo获得计算规则rule
			Excel excelJson = excelDao.findByNo(templateNo);
	        String rules = excelJson.getRules();
	        JSONObject ruleObj=JSONObject.fromObject(rules);
	        Integer cellNumber=ruleObj.getInt("cellNumber");
	        JSONArray rows = ruleObj.getJSONArray("rows");
	        //System.out.println("rows:"+rows);
	        //定义打印页面的高度和宽度
	        int htmlHeidth = 842;
	        Double pageRows=30.0;
	        Integer headRows=0;
	        Integer footRows=0;       	        
	        int dynamicRows=0;
	        int page=1;
	        
	        StringBuffer headRowsInfo = new StringBuffer();
	        ArrayList<String> dynamicRowsInfo = new ArrayList<String>();
	        StringBuffer dynamicRowsStr = new StringBuffer();  
	        StringBuffer footRowsInfo = new StringBuffer();
	        StringBuffer nomalRowsInfo=new StringBuffer();
	        for(int i = 0; i < rows.size(); i++) {
	        	JSONObject rowObj = rows.getJSONObject(i);
	        	String type="";
	        	if(rowObj.has("type")){
		        	type = (String)rowObj.get("type");
	        	}
	        	String isDynamic = (String)rowObj.get("isDynamic");
	        	if(isDynamic!=null&&!isDynamic.equals("")){
	        	 	String listKey = (String)rowObj.get("listKey");
	        	 	if(listKey==null) continue;
	        	 	JsonElement json=getElementInObj(listKey,obj);
	        	 	JSONArray viewList=JSONArray.fromObject(json.toString());
	        	 	dynamicRows=viewList.size();
		        	JSONArray cols = (JSONArray)rowObj.get("cols");
        	        StringBuffer tableHead = new StringBuffer();
        	        tableHead.append("<tr>");
		        	for(int q=0;q<cols.size();q++){
    	        		JSONObject colObj = cols.getJSONObject(q);
    	        		String colspan = (String) colObj.get("colspan");
    	        		int cs = Integer.parseInt(colspan);
    	        		String keyName = (String) colObj.get("keyName");
    	        		if(keyName != null){
    	        			tableHead.append("<td style='text-align:center;' height='20px' colspan='" + cs + "' width='20%'>"+ keyName +"</td>");
    	        		}
		        	}
    	        	tableHead.append("</tr>");	 
    	        	headRowsInfo.append(tableHead);
	        		for(int x=0;x<viewList.size();x++){
	        			JSONObject viewRow=viewList.getJSONObject(x);
	        	        StringBuffer dynamicRow = new StringBuffer();
	        	        dynamicRow.append("<tr>");
	    	        	for (int j = 0; j < cols.size(); j++) {
	    	        		String value="";
	    	        		JSONObject colObj = cols.getJSONObject(j);
	    	        		String colspan = (String) colObj.get("colspan");
	    	        		int cs = Integer.parseInt(colspan);
	    	        		String key = (String) colObj.get("key");
                            if(key != null){
	    	        			if(viewRow.has(key)){
		    	        			value =viewRow.getString(key);    
		    	        			if(value==null||value.equals("null")||value.equals("")) value="";
	    	        			}
	    	        			dynamicRow.append("<td style='text-align:center;' width='20%' colspan='" + cs + "'><div width='100px'>"+value+"</div></td>");
	    	        		}
	    				}
	    	        	dynamicRow.append("</tr>");
	    	        	dynamicRowsStr.append(dynamicRow);
	    	        	dynamicRowsInfo.add(dynamicRow.toString());
	        		}
	        	}

		        	if(isDynamic!=null&&!isDynamic.equals("")){
		        		continue;
		        	}
		        	JSONArray cols = (JSONArray)rowObj.get("cols");
		        	if(type.equals("header")){
			        	headRowsInfo.append("<tr>");       		
		        	}
		        	if(type.equals("footer")){
			        	footRowsInfo.append("<tr>");       		
		        	}
		        	else{
		        		 nomalRowsInfo.append("<tr>");
		        	}
		        	for (int j = 0; j < cols.size(); j++) {
		        		String value="";
		        		JSONObject colObj = cols.getJSONObject(j);
		        		String colspan = (String) colObj.get("colspan");
		        		int cs = Integer.parseInt(colspan);
		        		String key = (String) colObj.get("key");
		        		String keyName = (String) colObj.get("keyName");
		        		Object commentNoObj = (String)colObj.get("commentNo");
		        		String commentNo = commentNoObj != null ? (String) commentNoObj : "";
		        		if(keyName != null){
		        		    String info="<td style='text-align:center;' height='20px' colspan='" + cs + "' width='"+cs*50+"px'>"+ keyName +"</td>";
				        	if(type.equals("header")){
			        			headRowsInfo.append(info);
				        	}
				        	if(type.equals("footer")){
			        			footRowsInfo.append(info);
				        	}else{
				        		nomalRowsInfo.append(info);
				        	}
		        		}else if(key != null){
		        			value = getValueInObjByHtml(key,commentNo,obj,0);
		        			String info="<td style='text-align:center;' height='20px' colspan='" + cs + "' width='"+cs*50+"px'>"+value+"</td>";
				        	if(type.equals("header")){
			        			headRowsInfo.append(info);
				        	}
				        	if(type.equals("footer")){
			        			footRowsInfo.append(info);
				        	}else{
				        		nomalRowsInfo.append(info);
				        	}
		        		}
					}
		        	if(type.equals("header")){
			        	headRowsInfo.append("</tr>");		        	
		        		headRows++;	        		
		        	}
		        	if(type.equals("footer")){
			        	footRowsInfo.append("</tr>");		        	
		        		footRows++;	        		
		        	}else{
			        	nomalRowsInfo.append("</tr>");
		        	}
	        }

	        if(dynamicRowsInfo.size()>0){
	    		double pages=(double)((double)(dynamicRowsInfo.size())/pageRows);//转换为天
	    		page=(int) Math.ceil(pages);//转化为整数
	    		if(page<=0){
	    			page=1;
	    		}	 
	        }
	        html.append(nomalRowsInfo.toString());
	        for (int i=1;i<=page;i++){
	        	if(i==1){
		            html.append("<tr><td colspan='"+cellNumber+"'><div><table width='1000px' cellspacing='0' height='930px' cellpadding='0'>");        		
	        	}
	        	else{
		            html.append("<tr><td colspan='"+cellNumber+"'><div style='page-break-after:always'><table width='1000px' cellspacing='0' height='930px' cellpadding='0'>");        		     		       		
	        	}
	            html.append(headRowsInfo);
	            for(int j=0;j<pageRows.intValue();j++){
	            	int index=(i-1)*pageRows.intValue()+j;
	            	if(index>(dynamicRowsInfo.size()-1)) break;
	            	String dynamicRow=dynamicRowsInfo.get(index);
	            	html.append(dynamicRow);
	            }
	            //html.append(dynamicRowsStr);
	            html.append(footRowsInfo);
	            html.append("</table></div></td></tr>");
	        }
            html.append("</table></div>");
	        html.append("</body></html>");  
	        System.out.println(html.toString());
	        renderer.setDocumentFromString(html.toString()); 
	        renderer.layout();
	        renderer.createPDF(os);  
	        os.close(); 
		} catch (FileNotFoundException e) {
			e.printStackTrace();
			return "";
		} catch (DocumentException e) {
			e.printStackTrace();
			return "";
		} catch (IOException e) {
			e.printStackTrace();
			return "";
		}
		return templateNo+".pdf";
	}
	
	
	public String createHtmlToPdf_bak(String templateNo, String attachmentLink, String fileFullName, JsonObject obj){

		String fileurl = xpnPlatformConfig.getAttachment().getRoot();
		String output=fileurl+"/patrol_print/";
		File file = new File(output);
		if(file.exists()){
			file.delete();
		}
		file.mkdirs();
		String furl = "C:/Windows/Fonts/msyh.ttf";
		file = new File(furl);
		if(!file.exists()){
			furl = "C:/Windows/Fonts/msyh.ttc";
		}
		String outputFile = output+templateNo+".pdf";  
        OutputStream os;
		try {
			os = new FileOutputStream(outputFile);
			ITextRenderer renderer = new ITextRenderer();  
	        ITextFontResolver fontResolver = renderer.getFontResolver();    
	        fontResolver.addFont(furl, BaseFont.IDENTITY_H, BaseFont.NOT_EMBEDDED);  
	        StringBuffer html = new StringBuffer();
	     // DOCTYPE 必需写否则类似于 这样的字符解析会出现错误  
	        html.append("<!DOCTYPE html PUBLIC '-//W3C//DTD XHTML 1.0 Transitional//EN' 'http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd'>");  
	        html.append("<html xmlns='http://www.w3.org/1999/xhtml'>").append("<head>")  
	            .append("<meta http-equiv='Content-Type' content='text/html; charset=UTF-8' />")  
	            .append("<style type='text/css'>@page{size:297mm 297mm;}body "
	            		+ "{font-family:'Microsoft YaHei';font-weight:bold;font-size:16px;}table{border:1px solid #000;}"
	            		+ "td{border:1px solid #000;text-align:left;}.imgdiv{border-top: 1px solid #000;"
	            		+ "border-left:1px solid #000;border-right: 1px solid #000;text-align:center;}</style>")  
	            .append("</head>")  
	            .append("<body>")
	            .append("<table width='1000px' cellspacing='0' height='930px' cellpadding='0'>");  
	 
	        
	        
	    	// 1 根据模板编号templateNo获得计算规则rule
			Excel excelJson = excelDao.findByNo(templateNo);
	        String rules = excelJson.getRules();
	        JSONArray rows = JSONArray.fromObject(rules);
	        //System.out.println("rows:"+rows);
	        //定义打印页面的高度和宽度
	        int htmlHeidth = 842;
	        
	        Integer perPageRows=50;
	        
	        Integer headRows=0;
	        
	        Integer allRows=rows.size();
    		
	        for(int i = 0; i < rows.size(); i++) {
	        	JSONObject rowObj = rows.getJSONObject(i);
	        	String isTitle = (String)rowObj.get("isTitle");
	        	if(isTitle!=null&&!isTitle.equals("")){
	        		headRows++;
	        	}
	        }
	        
	        //得到行属性
	        for(int i = 0; i < rows.size(); i++) {
	        	JSONObject rowObj = rows.getJSONObject(i);
	        	String isTitle = (String)rowObj.get("isTitle");
	        	String type = (String)rowObj.get("type");
	        	/*Integer height = (Integer)rowObj.get("height");*/
	        	/*Integer rowIndex = (Integer)rowObj.get("rowIndex");*/
	        	JSONArray cols = (JSONArray)rowObj.get("cols");
	        	String isDynamic = (String)rowObj.get("isDynamic");
	       
	        	//得到列的属性
	        	String value = "";
	        	if(isDynamic!=null&&!isDynamic.equals("")){
	        	 	String listKey = (String)rowObj.get("listKey");
	        	 	JsonElement json=getElementInObj(listKey,obj);
	        	 	JSONArray viewList=JSONArray.fromObject(json.toString());
	        	 	String tr="";
  
	        		for(int x=0;x<viewList.size();x++){
	        			JSONObject viewRow=viewList.getJSONObject(x);
	    	        	html.append("<tr>");
	      	        	tr+="<tr>";
	    	        	for (int j = 0; j < cols.size(); j++) {
	    	        		JSONObject colObj = cols.getJSONObject(j);
	    	        		String colspan = (String) colObj.get("colspan");
	    	        		int cs = Integer.parseInt(colspan);
	    	        		String key = (String) colObj.get("key");
	    	        		String keyName = (String) colObj.get("keyName");
	    	        		Object commentNoObj = (String)colObj.get("commentNo");
	    	        		String commentNo = commentNoObj != null ? (String) commentNoObj : "";
	    	        		if(keyName != null){
	    	        			html.append("<td style='text-align:center;' height='20px' colspan='" + cs + "' width='"+cs*30+"px'>"+ keyName +"</td>");
	    	        		}else if(key != null){
	    	        			if(viewRow.has(key)){
		    	        			value =viewRow.getString(key);    
		    	        			if(value==null||value.equals("null")||value.equals("")) value="";
	    	        			}
	    	        			tr+="<td style='text-align:center;' height='20px' colspan='" + cs + "' width='"+cs*30+"px'>"+value+"</td>";
	    	        			html.append("<td style='text-align:center;' height='20px' colspan='" + cs + "' width='"+cs*30+"px'>"+value+"</td>");
	    	        		}
	    				}
	    	        	tr+="</tr>";
	    	        	html.append("</tr>");
	        		}
	        		for(int xx=0;xx<50;xx++){
	        			html.append(tr);
	        		}
	        	}else{
		        	html.append("<tr>");
		        	for (int j = 0; j < cols.size(); j++) {
		        		JSONObject colObj = cols.getJSONObject(j);
		        		String colspan = (String) colObj.get("colspan");
		        		int cs = Integer.parseInt(colspan);
		        		String key = (String) colObj.get("key");
		        		String keyName = (String) colObj.get("keyName");
		        		Object commentNoObj = (String)colObj.get("commentNo");
		        		String commentNo = commentNoObj != null ? (String) commentNoObj : "";
		        		if(keyName != null){
		        			html.append("<td style='text-align:center;' height='20px' colspan='" + cs + "' width='"+cs*50+"px'>"+ keyName +"</td>");
		        		}else if(key != null){
		        			value = getValueInObjByHtml(key,commentNo,obj,0);
		        			html.append("<td style='text-align:center;' height='20px' colspan='" + cs + "' width='"+cs*50+"px'>"+value+"</td>");
		        		}
					}
		        	html.append("</tr>");	        		
	        	}
			}
	        html
	        .append("</table>")
	        .append("</body></html>");  
	        System.out.println(html.toString());
	        renderer.setDocumentFromString(html.toString()); 
	        renderer.layout();
	        renderer.createPDF(os);  
	        os.close(); 
		} catch (FileNotFoundException e) {
			e.printStackTrace();
			return "";
		} catch (DocumentException e) {
			e.printStackTrace();
			return "";
		} catch (IOException e) {
			e.printStackTrace();
			return "";
		}
		return templateNo+".pdf";
	}

	private String getValueInObjByHtml(String key, String commentNo, JsonObject obj,Integer round) {
		String value = "";
		JsonElement resultObj = getElementInObj(key, obj);
		
		if (resultObj != null && !resultObj.isJsonNull() && !resultObj.isJsonArray() && !resultObj.isJsonObject()){
			value=resultObj.getAsString();
			try {
				if(round!=null&&round>0){
					StringBuilder format=new StringBuilder();
					format.append("#0.");
					for (int i=0;i<round;i++){
						format.append("0");
					}
					DecimalFormat df   = new DecimalFormat(format.toString());
					Double resultDouble=resultObj.getAsDouble();
					value=df.format(resultDouble).toString();
				}
			} catch (Exception e){
				e.printStackTrace();
			}
		}

		// 如果有意见编号，就要取意见
		if (commentNo != null && !commentNo.equals("") && resultObj != null && !resultObj.isJsonNull()
				&& resultObj.isJsonArray()) {
			// 获取待办
			JsonArray todoList = resultObj.getAsJsonArray();
			value = "";
			StringBuffer bufferReturn = new StringBuffer();
			// 从第一条待办中获取意见
			if (todoList != null && todoList.size() > 0) {
				JsonObject todo = todoList.get(0).getAsJsonObject();
				JsonArray comments = todo.get("comments").isJsonNull()?new JsonArray():todo.get("comments").getAsJsonArray();
				//通过状态id分组,只找出不同状态下的最有一条意见打印。
				Map<String,StringBuffer> mapByStateId = new HashMap<>();
				for (JsonElement o : comments) {
					JsonObject comment = o.getAsJsonObject();
					JsonObject commentColumn = comment.getAsJsonObject("column");
					String no = commentColumn.get("no").getAsString();
					if (no.equals(commentNo)) {
						String stateId ="-1";
						JsonObject todoInComments = comment.getAsJsonObject("todo");
						JsonObject state = todoInComments!=null?todoInComments.getAsJsonObject("state"):null;
						stateId = state!=null?state.get("id").getAsString():"-1";
						StringBuffer buffer = new StringBuffer();
						buffer.append(comment.get("content") == null || comment.get("content").isJsonNull() ? ""
								: comment.get("content").getAsString());
						buffer.append("             ");
						buffer.append(comment.get("userName") == null || comment.get("userName").isJsonNull() ? ""
								: comment.get("userName").getAsString());
						buffer.append("/");
						buffer.append(comment.get("userName") == null || comment.get("userName").isJsonNull() ? ""
								: comment.get("userOrgName").getAsString().split("/")[0]);
						buffer.append("    ");
						buffer.append(comment.get("createdTime") == null || comment.get("createdTime").isJsonNull() ? ""
								: comment.get("createdTime").getAsString());
						mapByStateId.put(stateId,buffer);
					}
				}
				Set<String> stringSet = mapByStateId.keySet();
				for(String keyStateId:stringSet){
					if(bufferReturn.toString().equals("")){
						bufferReturn = mapByStateId.get(keyStateId);
					}else{
						bufferReturn.append("\n");
						bufferReturn.append(mapByStateId.get(keyStateId));
					}
				}
			}
			value = bufferReturn.toString();
		}
		return value;
	}
	
	
	
	//创建打印html文件 巡检图片排版方式为：统一放在打印最后统一处理  2019-09-16 zyk 图片统一排版到最后
	//@Override
	public String create_print_html_bak(String templateNo, String attachmentLink, String fileFullName, JsonObject obj){

		String fileurl = xpnPlatformConfig.getAttachment().getRoot();
		String output=fileurl+"/patrol_print/";
		File file = new File(output);
		if(file.exists()){
			file.delete();
		}
		file.mkdirs();
		String furl = "C:/Windows/Fonts/msyh.ttf";
		file = new File(furl);
		if(!file.exists()){
			furl = "C:/Windows/Fonts/msyh.ttc";
		}
		String outputFile = output+templateNo+".html";  
        OutputStream os;
		try {
			File f=new File(outputFile);
			if(f.exists()){f.delete();};
			os = new FileOutputStream(outputFile);
	        StringBuffer html = new StringBuffer();
	        List<JSONObject> picLinks=new ArrayList<JSONObject>();
	        //DOCTYPE 必需写否则类似于 这样的字符解析会出现错误  
	        html.append("<!DOCTYPE html><html lang='zh-CN'><head><meta charset='gb2312'><meta http-equiv='X-UA-Compatible' content='IE=edge'>"
	        		+ "<meta name='viewport' content='width=device-width, initial-scale=1'><title>巡检报告</title>"
	        		+ " <link href='/bootstrap.min.css' rel='stylesheet'></head><style>"
	        		+ ".a4-endwise{width: 1075px;height: 1567px;border: 1px #000 solid;overflow: hidden;padding: 0;word-break:break-all;}"
	        		+ ".a4-broadwise{width: 1569px;height: 1073px;border: 1px #000 solid;overflow: hidden;padding: 0;word-break:break-all;}"
	        		+ ".print{position: fixed;top: 1%;right: 10%;}table{border:1px solid #000;table-layout:fixed;word-wrap:break-strict;}"
	        		+ "table{border:1px solid #000;text-align:center;word-wrap:break-word}"
	        		+ "td{border:1px solid #000;text-align:center;word-wrap:break-word}"
	        		+ ".imgdiv{border-top: 1px solid #000;border:1px solid #000;text-align:center;}</style>"
	            		+ "<script>"
	            		+ "function preview() "
	            		+ "{bdhtml=window.document.body.innerHTML;"
	            		+ "sprnstr='<!--startprint-->';eprnstr='<!--endprint-->';"
	            		+ "prnhtml=bdhtml.substring(bdhtml.indexOf(sprnstr));"
	            		+ "prnhtml=prnhtml.substring(0,prnhtml.indexOf(eprnstr)); "
	            		+ "window.document.body.innerHTML=prnhtml;"
	            		+ "window.print();window.document.body.innerHTML=bdhtml;}</script><body>");
	        String testHtml=html.toString();
            html.append("<div class='container' style='width:1075px'><div><button onClick='preview()' style='width:1075px;height:30px' >打印巡检报告</button></div><!--startprint--><table style='width:1075px' cellspacing='0' height='100%' cellpadding='0'>");  	        
	    	// 1 根据模板编号templateNo获得计算规则rule
			Excel excelJson = excelDao.findByNo(templateNo);
	        String rules = excelJson.getRules();
	        JSONObject ruleObj=JSONObject.fromObject(rules);
	        Integer cellNumber=ruleObj.getInt("cellNumber");
	        JSONArray rows = ruleObj.getJSONArray("rows");
	        //System.out.println("rows:"+rows);
	        //定义打印页面的高度和宽度
	        int htmlHeidth = 842;
	        Double pageRows=25.0;
	        Integer headRows=0;
	        Integer footRows=0;       	        
	        int dynamicRows=0;
	        int page=1;
	        
	        StringBuffer headRowsInfo = new StringBuffer();
	        ArrayList<String> dynamicRowsInfo = new ArrayList<String>();
	        StringBuffer dynamicRowsStr = new StringBuffer();  
	        StringBuffer footRowsInfo = new StringBuffer();
	        StringBuffer nomalRowsInfo=new StringBuffer();
	        for(int i = 0; i < rows.size(); i++) {
	        	JSONObject rowObj = rows.getJSONObject(i);
	        	String type="";
	        	if(rowObj.has("type")){
		        	type = (String)rowObj.get("type");
	        	}
	        	String isDynamic = (String)rowObj.get("isDynamic");
	        	if(isDynamic!=null&&!isDynamic.equals("")){
	        	 	String listKey = (String)rowObj.get("listKey");
	        	 	if(listKey==null) continue;
	        	 	JsonElement json=getElementInObj(listKey,obj);
	        	 	JSONArray viewList=JSONArray.fromObject(json.toString());
	        	 	dynamicRows=viewList.size();
		        	JSONArray cols = (JSONArray)rowObj.get("cols");
        	        StringBuffer tableHead = new StringBuffer();
        	        if(rowObj.has("style")){
        	        	tableHead.append("<tr style="+rowObj.getString("style")+">");
        	        }else{
            	        tableHead.append("<tr>"); 	
        	        }
		        	for(int q=0;q<cols.size();q++){
    	        		JSONObject colObj = cols.getJSONObject(q);
    	        		String colspan = (String) colObj.get("colspan");
    	        		int cs = Integer.parseInt(colspan);
    	        		String keyName = (String) colObj.get("keyName");
    	        		if(keyName != null){
	        	        	tableHead.append("<td style='text-align:center;' height='20px' colspan='" + cs + "'>"+ keyName +"</td>");
    	        	        /*if(colObj.has("style")){
    	        	        	tableHead.append("<td style='"+colObj.getString("style")+"' height='20px' colspan='" + cs + "'>"+ keyName +"</td>");
    	        	        }else{
    	        	        	tableHead.append("<td style='text-align:center;' height='20px' colspan='" + cs + "' width='20%'>"+ keyName +"</td>");
    	        	        }*/   
    	        			
    	        		}
		        	}
    	        	tableHead.append("</tr>");	 
    	        	headRowsInfo.append(tableHead);
	        		for(int x=0;x<viewList.size();x++){
	        			JSONObject viewRow=viewList.getJSONObject(x);
	        	        StringBuffer dynamicRow = new StringBuffer();
	        	        dynamicRow.append("<tr>");
	        	        if(viewRow.containsKey("picLinks")){//处理巡检图片信息
	        	        	if(viewRow.getString("picLinks")!=null&&!viewRow.getString("picLinks").equals("")){
	        	        		String[] picArray=viewRow.getString("picLinks").split(";");
	        	        		for(String pic:picArray){
	        	        			JSONObject po=new JSONObject();
	        	        			String name=viewRow.getString("indexItemEquipment")+viewRow.getString("indexItemContent");
	        	        			po.put("name", name);
                                    po.put("link", pic);
                                    picLinks.add(po);
	        	        		}
	        	        	}
	        	        }
	    	        	for (int j = 0; j < cols.size(); j++) {
	    	        		String value="";
	    	        		JSONObject colObj = cols.getJSONObject(j);
	    	        		String colspan = (String) colObj.get("colspan");
	    	        		int cs = Integer.parseInt(colspan);
	    	        		String key = (String) colObj.get("key");
                            if(key != null){
	    	        			if(viewRow.has(key)){
		    	        			value =viewRow.getString(key);    
		    	        			if(value==null||value.equals("null")||value.equals("")) value="";
	    	        			}
	    	        			dynamicRow.append("<td style='text-align:center;' width='20%' colspan='" + cs + "'>"+value+"</td>");
	    	        		}
	    				}
	    	        	dynamicRow.append("</tr>");
	    	        	dynamicRowsStr.append(dynamicRow);
	    	        	dynamicRowsInfo.add(dynamicRow.toString());
	        		}
	        	}

		        	if(isDynamic!=null&&!isDynamic.equals("")){
		        		continue;
		        	}
		        	JSONArray cols = (JSONArray)rowObj.get("cols");
		        	if(type.equals("header")){
	        	        if(rowObj.has("style")){
	        	        	headRowsInfo.append("<tr style="+rowObj.getString("style")+">");
	        	        }else{
	        	        	headRowsInfo.append("<tr>"); 	
	        	        }       		
		        	}
		        	if(type.equals("footer")){
	        	        if(rowObj.has("style")){
	        	        	footRowsInfo.append("<tr style="+rowObj.getString("style")+">");
	        	        }else{
	        	        	footRowsInfo.append("<tr>"); 	
	        	        }        		
		        	}
		        	else{
	        	        if(rowObj.has("style")){
	        	        	nomalRowsInfo.append("<tr style="+rowObj.getString("style")+">");
	        	        }else{
	        	        	nomalRowsInfo.append("<tr>"); 	
	        	        }    
		        	}
		        	for (int j = 0; j < cols.size(); j++) {
		        		String value="";
		        		JSONObject colObj = cols.getJSONObject(j);
		        		String colspan = (String) colObj.get("colspan");
		        		int cs = Integer.parseInt(colspan);
		        		String key = (String) colObj.get("key");
		        		String keyName = (String) colObj.get("keyName");
		        		Object commentNoObj = (String)colObj.get("commentNo");
		        		String commentNo = commentNoObj != null ? (String) commentNoObj : "";
		        		if(keyName != null){
		        		    String info="<td style='text-align:center;' height='20px' colspan='" + cs + "' width='"+cs*50+"px'>"+ keyName +"</td>";
				        	if(type.equals("header")){
			        			headRowsInfo.append(info);
				        	}
				        	if(type.equals("footer")){
			        			footRowsInfo.append(info);
				        	}else{
				        		nomalRowsInfo.append(info);
				        	}
		        		}else if(key != null){
		        			value = getValueInObjByHtml(key,commentNo,obj,0);
		        			String info="<td style='text-align:center;' height='20px' colspan='" + cs + "' width='"+cs*50+"px'>"+value+"</td>";
				        	if(type.equals("header")){
			        			headRowsInfo.append(info);
				        	}
				        	if(type.equals("footer")){
			        			footRowsInfo.append(info);
				        	}else{
				        		nomalRowsInfo.append(info);
				        	}
		        		}
					}
		        	if(type.equals("header")){
			        	headRowsInfo.append("</tr>");		        	
		        		headRows++;	        		
		        	}
		        	if(type.equals("footer")){
			        	footRowsInfo.append("</tr>");		        	
		        		footRows++;	        		
		        	}else{
			        	nomalRowsInfo.append("</tr>");
		        	}
	        }

	        if(dynamicRowsInfo.size()>0){
	    		double pages=(double)((double)(dynamicRowsInfo.size())/pageRows);//向上取整
	    		page=(int) Math.ceil(pages);//转化为整数
	    		if(page<=0){
	    			page=1;
	    		}	 
	        }
	        html.append(nomalRowsInfo.toString());
	        for (int i=1;i<=page;i++){
        		if(i==page){
		            html.append("<tr><td colspan='"+cellNumber+"'><div class='a4-endwise'><table width='100%'  cellspacing='0'  cellpadding='0'>");        		     		       		
   			
        		}else{
		            html.append("<tr><td colspan='"+cellNumber+"'><div class='a4-endwise' style='page-break-after:always'><table width='100%'  cellspacing='0'  cellpadding='0'>");        		     		       		      			
        		}
	            html.append(headRowsInfo);
	            for(int j=0;j<pageRows.intValue();j++){
	            	int index=(i-1)*pageRows.intValue()+j;
	            	if(index>(dynamicRowsInfo.size()-1)) break;
	            	String dynamicRow=dynamicRowsInfo.get(index);
	            	html.append(dynamicRow);
	            }
	            html.append(footRowsInfo);
	            html.append("</table></div></td></tr></table>");
	        }
	        if(picLinks.size()>0){//处理巡检图片信息
	        	double pagesize=6;
	    		double picpages=Math.ceil((double)(picLinks.size())/pagesize);//一页最多四张照片
	    		int pageNum=(int) picpages;
	    		for(int k=0;k<pageNum;k++){
	    			if(k==pageNum-1){
	    				html.append("<div class='a4-endwise'>");
	    			}
	    			else{
	    				html.append("<div class='a4-endwise' style='page-break-after:always'>");	
	    			}
	    			for(int m=0;m<pagesize;m++){
	    				int index=(int)(k*pagesize)+m;
	    				if(index<picLinks.size()){
			    			html.append("<div style='height:500px;width:490px;padding-bottom:5px;float:left'>");
		    				JSONObject po=picLinks.get(index);
				        	html.append("<div style='width:99%;padding:1px'><img style='height:450px;width:430px' src='"+po.getString("link")+"'/></div>");
				        	html.append("<div style='width:99%;font-size:12px;text-align:center;padding-bottom:1px'>"+po.getString("name")+"</div>"); 	
				        	html.append("</div>");  					
	    				}

	    			}
		        	html.append("</div>");
	    		}

	        }
	        
	        testHtml=testHtml+("  <body><a class='print' href='javascript:;' onclick='preview();'>打印</a><!--startprint--><div class='container a4-endwise' id='test' style='page-break-after:always'>1111111111111111 </div><div class='container a4-endwise' style='page-break-after:always'>2222222222222222</div><div class='container a4-endwise'>33333333333333333</div><!--endprint--></body></html>");
            html.append("<!--endprint--></div>");
	        html.append("</body></html>"); 
	        os.write( html.toString().getBytes() );
	        System.out.println(html.toString());
	        //os.write( testHtml.toString().getBytes() );
	        //System.out.println(testHtml.toString());
	        os.close(); 
		} catch (FileNotFoundException e) {
			e.printStackTrace();
			return "";
		} catch (IOException e) {
			e.printStackTrace();
			return "";
		}
		return templateNo+".html";
	}
	
	
	//创建打印html文件 巡检图片排版方式为直接放第一张图片在项最后一列 2019-12-01 zyk
	@Override
	public String create_print_html(String templateNo, String attachmentLink, String fileFullName, JsonObject obj){

		String fileurl = xpnPlatformConfig.getAttachment().getRoot();
		String output=fileurl+"/patrol_print/";
		File file = new File(output);
		if(file.exists()){
			file.delete();
		}
		file.mkdirs();
		String furl = "C:/Windows/Fonts/msyh.ttf";
		file = new File(furl);
		if(!file.exists()){
			furl = "C:/Windows/Fonts/msyh.ttc";
		}
		String outputFile = output+templateNo+".html";  
        OutputStream os;
		try {
			File f=new File(outputFile);
			if(f.exists()){f.delete();};
			os = new FileOutputStream(outputFile);
	        StringBuffer html = new StringBuffer();
	        List<JSONObject> picLinks=new ArrayList<JSONObject>();
	        //DOCTYPE 必需写否则类似于 这样的字符解析会出现错误  
	        html.append("<!DOCTYPE html><html lang='zh-CN'><head><meta charset='utf-8'><meta http-equiv='X-UA-Compatible' content='IE=edge'>"
	        		+ "<meta name='viewport' content='width=device-width, initial-scale=1'><title>巡检报告</title>"
	        		+ " <link href='/bootstrap.min.css' rel='stylesheet'></head><style>"
	        		+ ".a4-endwise{width: 1075px;height: 1567px;border: 1px #000 solid;overflow: hidden;padding: 0;word-break:break-all;}"
	        		+ ".a4-broadwise{width: 1569px;height: 1073px;border: 1px #000 solid;overflow: hidden;padding: 0;word-break:break-all;}"
	        		+ ".print{position: fixed;top: 1%;right: 10%;}table{border:1px solid #000;table-layout:fixed;word-wrap:break-strict;}"
	        		+ "table{border:1px solid #000;text-align:center;word-wrap:break-word}"
	        		+ "td{border:1px solid #000;text-align:center;word-wrap:break-word}"
	        		+ ".imgdiv{border-top: 1px solid #000;border:1px solid #000;text-align:center;}</style>"
	            		+ "<script>"
	            		+ "function preview() "
	            		+ "{bdhtml=window.document.body.innerHTML;"
	            		+ "sprnstr='<!--startprint-->';eprnstr='<!--endprint-->';"
	            		+ "prnhtml=bdhtml.substring(bdhtml.indexOf(sprnstr));"
	            		+ "prnhtml=prnhtml.substring(0,prnhtml.indexOf(eprnstr)); "
	            		+ "window.document.body.innerHTML=prnhtml;"
	            		+ "window.print();window.document.body.innerHTML=bdhtml;}</script><body>");
	        String testHtml=html.toString();
            html.append("<div class='container' style='width:1075px'><div><button onClick='preview()' style='width:1075px;height:30px' >打印巡检报告</button></div><!--startprint--><table style='width:1075px' cellspacing='0' height='100%' cellpadding='0'>");  	        
	    	// 1 根据模板编号templateNo获得计算规则rule
			Excel excelJson = excelDao.findByNo(templateNo);
	        String rules = excelJson.getRules();
	        JSONObject ruleObj=JSONObject.fromObject(rules);
	        Integer cellNumber=ruleObj.getInt("cellNumber");
	        JSONArray rows = ruleObj.getJSONArray("rows");
	        //System.out.println("rows:"+rows);
	        //定义打印页面的高度和宽度
	        int htmlHeidth = 842;
	        Double pageRows=25.0;
	        Integer headRows=0;
	        Integer footRows=0;       	        
	        int dynamicRows=0;
	        int page=1;
	        
	        StringBuffer headRowsInfo = new StringBuffer();
	        ArrayList<String> dynamicRowsInfo = new ArrayList<String>();
	        StringBuffer dynamicRowsStr = new StringBuffer();  
	        StringBuffer footRowsInfo = new StringBuffer();
	        StringBuffer nomalRowsInfo=new StringBuffer();
	        for(int i = 0; i < rows.size(); i++) {
	        	JSONObject rowObj = rows.getJSONObject(i);
	        	String type="";
	        	if(rowObj.has("type")){
		        	type = (String)rowObj.get("type");
	        	}
	        	String isDynamic = (String)rowObj.get("isDynamic");
	        	if(isDynamic!=null&&!isDynamic.equals("")){
	        	 	String listKey = (String)rowObj.get("listKey");
	        	 	if(listKey==null) continue;
	        	 	JsonElement json=getElementInObj(listKey,obj);
	        	 	JSONArray viewList=JSONArray.fromObject(json.toString());
	        	 	dynamicRows=viewList.size();
		        	JSONArray cols = (JSONArray)rowObj.get("cols");
        	        StringBuffer tableHead = new StringBuffer();
        	        if(rowObj.has("style")){
        	        	tableHead.append("<tr style="+rowObj.getString("style")+">");
        	        }else{
            	        tableHead.append("<tr>"); 	
        	        }
		        	for(int q=0;q<cols.size();q++){
    	        		JSONObject colObj = cols.getJSONObject(q);
    	        		String colspan = (String) colObj.get("colspan");
    	        		int cs = Integer.parseInt(colspan);
    	        		String keyName = (String) colObj.get("keyName");
    	        		if(keyName != null){
	        	        	//tableHead.append("<td style='text-align:center;' height='20px' colspan='" + cs + "'>"+ keyName +"</td>");
    	        	        if(colObj.has("style")){
    	        	        	tableHead.append("<td style='"+colObj.getString("style")+"' colspan='" + cs + "'>"+ keyName +"</td>");
    	        	        }else{
    	        	        	tableHead.append("<td style='text-align:center;' height='20px' colspan='" + cs + "' width='20%'>"+ keyName +"</td>");
    	        	        }
    	        			
    	        		}
		        	}
    	        	tableHead.append("</tr>");	 
    	        	headRowsInfo.append(tableHead);
	        		for(int x=0;x<viewList.size();x++){
	        			JSONObject viewRow=viewList.getJSONObject(x);
	        	        StringBuffer dynamicRow = new StringBuffer();
	        	        dynamicRow.append("<tr>");
	        	        if(viewRow.containsKey("picLinks")){//处理巡检图片信息
	        	        	if(viewRow.getString("picLinks")!=null&&!viewRow.getString("picLinks").equals("")){
	        	        		String[] picArray=viewRow.getString("picLinks").split(";");
	        	        		for(String pic:picArray){
	        	        			JSONObject po=new JSONObject();
	        	        			String name=viewRow.getString("indexItemEquipment")+viewRow.getString("indexItemContent");
	        	        			po.put("name", name);
                                    po.put("link", pic);
                                    picLinks.add(po);
	        	        		}
	        	        	}
	        	        }
	    	        	for (int j = 0; j < cols.size(); j++) {
	    	        		String value="";
	    	        		JSONObject colObj = cols.getJSONObject(j);
	    	        		String colspan = (String) colObj.get("colspan");
	    	        		int cs = Integer.parseInt(colspan);
	    	        		String key = (String) colObj.get("key");
	    	        		String imgInfo=null;
                            if(key != null){
	    	        			if(viewRow.has(key)){
	    	        				if(key.equals("picLinks")){
	    	        					if(viewRow.getString("picLinks")!=null&&!viewRow.getString("picLinks").equals("")){
	    	        						String[] picArray=viewRow.getString("picLinks").split(";");
	    	        						if(picArray.length>0&&picArray[0]!=null&&!picArray[0].equals("")){
	    	        							imgInfo="<img style='width:80px;height:70px'  src='"+picArray[0]+"'/>";
	    	        						}
	    	        					}
	    	        				}else{
			    	        			value =viewRow.getString(key);    
			    	        			if(value==null||value.equals("null")||value.equals("")) value="";   					
	    	        				}

	    	        			}
	    	        			if(imgInfo==null){
	        	        			dynamicRow.append("<td style='text-align:center;' width='20%' colspan='" + cs + "'>"+value+"</td>");	
	    	        			}else{
	        	        			dynamicRow.append("<td style='text-align:center;' colspan='" + cs + "'>"+imgInfo+"</td>");	       				
	    	        			}
	
	    	        		}
	    				}
	    	        	dynamicRow.append("</tr>");
	    	        	dynamicRowsStr.append(dynamicRow);
	    	        	dynamicRowsInfo.add(dynamicRow.toString());
	        		}
	        	}

		        	if(isDynamic!=null&&!isDynamic.equals("")){
		        		continue;
		        	}
		        	JSONArray cols = (JSONArray)rowObj.get("cols");
		        	if(type.equals("header")){
	        	        if(rowObj.has("style")){
	        	        	headRowsInfo.append("<tr style="+rowObj.getString("style")+">");
	        	        }else{
	        	        	headRowsInfo.append("<tr>"); 	
	        	        }       		
		        	}
		        	if(type.equals("footer")){
	        	        if(rowObj.has("style")){
	        	        	footRowsInfo.append("<tr style="+rowObj.getString("style")+">");
	        	        }else{
	        	        	footRowsInfo.append("<tr>"); 	
	        	        }        		
		        	}
		        	else{
	        	        if(rowObj.has("style")){
	        	        	nomalRowsInfo.append("<tr style="+rowObj.getString("style")+">");
	        	        }else{
	        	        	nomalRowsInfo.append("<tr>"); 	
	        	        }    
		        	}
		        	for (int j = 0; j < cols.size(); j++) {
		        		String value="";
		        		JSONObject colObj = cols.getJSONObject(j);
		        		String colspan = (String) colObj.get("colspan");
		        		int cs = Integer.parseInt(colspan);
		        		String key = (String) colObj.get("key");
		        		String keyName = (String) colObj.get("keyName");
		        		Object commentNoObj = (String)colObj.get("commentNo");
		        		String commentNo = commentNoObj != null ? (String) commentNoObj : "";
		        		if(keyName != null){
		        		    String info="<td style='text-align:center;' height='20px' colspan='" + cs + "' width='"+cs*50+"px'>"+ keyName +"</td>";
				        	if(type.equals("header")){
			        			headRowsInfo.append(info);
				        	}
				        	if(type.equals("footer")){
			        			footRowsInfo.append(info);
				        	}else{
				        		nomalRowsInfo.append(info);
				        	}
		        		}else if(key != null){
		        			value = getValueInObjByHtml(key,commentNo,obj,0);
		        			String info="<td style='text-align:center;' height='20px' colspan='" + cs + "' width='"+cs*50+"px'>"+value+"</td>";
				        	if(type.equals("header")){
			        			headRowsInfo.append(info);
				        	}
				        	if(type.equals("footer")){
			        			footRowsInfo.append(info);
				        	}else{
				        		nomalRowsInfo.append(info);
				        	}
		        		}
					}
		        	if(type.equals("header")){
			        	headRowsInfo.append("</tr>");		        	
		        		headRows++;	        		
		        	}
		        	if(type.equals("footer")){
			        	footRowsInfo.append("</tr>");		        	
		        		footRows++;	        		
		        	}else{
			        	nomalRowsInfo.append("</tr>");
		        	}
	        }

	        if(dynamicRowsInfo.size()>0){
	    		double pages=(double)((double)(dynamicRowsInfo.size())/pageRows);//向上取整
	    		page=(int) Math.ceil(pages);//转化为整数
	    		if(page<=0){
	    			page=1;
	    		}	 
	        }
	        html.append(nomalRowsInfo.toString());
	        for (int i=1;i<=page;i++){
        		if(i==page){
		            html.append("<tr><td colspan='"+cellNumber+"'><div class='a4-endwise'><table width='100%'  cellspacing='0'  cellpadding='0'>");        		     		       		
   			
        		}else{
		            html.append("<tr><td colspan='"+cellNumber+"'><div class='a4-endwise' style='page-break-after:always'><table width='100%'  cellspacing='0'  cellpadding='0'>");        		     		       		      			
        		}
	            html.append(headRowsInfo);
	            for(int j=0;j<pageRows.intValue();j++){
	            	int index=(i-1)*pageRows.intValue()+j;
	            	if(index>(dynamicRowsInfo.size()-1)) break;
	            	String dynamicRow=dynamicRowsInfo.get(index);
	            	html.append(dynamicRow);
	            }
	            html.append(footRowsInfo);
	            html.append("</table></div></td></tr></table>");
	        }
	        testHtml=testHtml+("  <body><a class='print' href='javascript:;' onclick='preview();'>打印</a><!--startprint--><div class='container a4-endwise' id='test' style='page-break-after:always'>1111111111111111 </div><div class='container a4-endwise' style='page-break-after:always'>2222222222222222</div><div class='container a4-endwise'>33333333333333333</div><!--endprint--></body></html>");
            html.append("<!--endprint--></div>");
	        html.append("</body></html>"); 
	        os.write( html.toString().getBytes() );
	        System.out.println(html.toString());
	        //os.write( testHtml.toString().getBytes() );
	        //System.out.println(testHtml.toString());
	        os.close(); 
		} catch (FileNotFoundException e) {
			e.printStackTrace();
			return "";
		} catch (IOException e) {
			e.printStackTrace();
			return "";
		}
		return templateNo+".html";
	}
	
}
